﻿using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Web;
using Oracle.DataAccess.Client;
using Sweepids.Models.Object;

namespace Sweepids.Models.Dao
{
    public class CategoryDao : Dao
    {
        /// <summary>
        /// Add a new Category to the database
        /// </summary>
        /// <param name="c">The Category to add</param>
        public void Create(Category c)
        {
            try
            {
                var myCommand = new OracleCommand("INSERT INTO SWEEP_CATEGORY " +
                                                  "(NAME) " +
                                                  "VALUES " +
                                                  "(:NAME)", myConnection);
                RunCommand(myCommand, c.OracleParameters, false);
            }
            catch (Exception e)
            {
                Debug.WriteLine("CategoryDao::Create(Category): " + e.Message);
                throw;
            }
        }

        /// <summary>
        /// Get category from category id
        /// </summary>
        /// <param name="catId">The category id</param>
        /// <returns>A Category</returns>
        public Category Read(decimal catId)
        {
            try
            {
                var result = new Category { CatId = catId };
                var myCommand = new OracleCommand("SELECT * FROM SWEEP_CATEGORY " +
                                                  "WHERE CAT_ID=:CAT_ID", myConnection);
                var myReader = RunCommand(myCommand, result.OracleParameters, true);
                while (myReader.Read())
                {
                    result = new Category(myReader);
                }
                myReader.Close();
                myConnection.Close();
                return result;
            }
            catch (Exception e)
            {
                Debug.WriteLine("CategoryDao::Read(int): " + e.Message);
                throw;
            }
        }
        public Category ReadByName(string name)
        {
            try
            {
                var result = new Category { Name=name };
                var myCommand = new OracleCommand("SELECT * FROM SWEEP_CATEGORY " +
                                                  "WHERE NAME=:NAME", myConnection);
                var myReader = RunCommand(myCommand, result.OracleParameters, true);
                while (myReader.Read())
                {
                    result = new Category(myReader);
                }
                myReader.Close();
                myConnection.Close();
                return result;
            }
            catch (Exception e)
            {
                Debug.WriteLine("CategoryDao::Read(int): " + e.Message);
                throw;
            }
        }

        /// <summary>
        /// Update a category in the database
        /// </summary>
        /// <param name="c">The category with the new information to update</param>
        public void Update(Category c)
        {
            try
            {
                var myCommand = new OracleCommand("UPDATE SWEEP_CATEGORY SET " +
                                                 "NAME=:NAME " +
                                                 "WHERE CAT_ID=:CAT_ID", myConnection);
                RunCommand(myCommand, c.OracleParameters, false);
            }
            catch (Exception e)
            {
                Debug.WriteLine("CategoryDao::Update(Category): " + e.Message);
                throw;
            }
        }

        /// <summary>
        /// Delete a Category from the database
        /// </summary>
        /// <param name="c">The Category to delete</param>
        public void Delete(Category c)
        {
            try
            {
                var myCommand = new OracleCommand("DELETE FROM SWEEP_CATEGORY " +
                                                 "WHERE CAT_ID=:CAT_ID", myConnection);
                RunCommand(myCommand, c.OracleParameters, false);
            }
            catch (Exception e)
            {
                Debug.WriteLine("CategoryDao::Delete(Category): " + e.Message);
                throw;
            }
        }

        public List<Category> ReadAll()
        {
            try
            {
                var categories = new List<Category>();
                var myCommand = new OracleCommand("SELECT * FROM SWEEP_CATEGORY ORDER BY NAME ASC", myConnection);
                var myReader = RunCommand(myCommand, true);
                while (myReader.Read())
                {
                    categories.Add(new Category(myReader));
                }
                myReader.Close();
                myConnection.Close();
                return categories;
            }
            catch (Exception e)
            {

                Debug.WriteLine("CategoryDao::ReadAll(): " + e.Message);
                throw;
            }
        }
    }
}